﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_MoveDownRotatingBanner')
BEGIN
    PRINT 'Dropping Procedure proc_cm_MoveDownRotatingBanner'
    DROP  Procedure  proc_cm_MoveDownRotatingBanner
END
GO

PRINT 'Creating Procedure proc_cm_MoveDownRotatingBanner'
GO

CREATE PROCEDURE [dbo].[proc_cm_MoveDownRotatingBanner]
	@pRotatingBannerId uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @vPriorityNum INT

	SELECT @vPriorityNum = [priority_num]
	FROM [dbo].[tbl_rotating_banner] (NOLOCK)
	WHERE [rotating_banner_id] = @pRotatingBannerId

	IF EXISTS (SELECT TOP 1 NULL FROM [dbo].[tbl_rotating_banner] WHERE [priority_num] > @vPriorityNum)
	BEGIN
		UPDATE [dbo].[tbl_rotating_banner]
		SET [priority_num] = [priority_num] - 1
		WHERE [priority_num] = @vPriorityNum + 1

		UPDATE [dbo].[tbl_rotating_banner]
		SET [priority_num] = @vPriorityNum + 1
		WHERE [rotating_banner_id] = @pRotatingBannerId
	END
END
GO

GRANT EXEC ON dbo.proc_cm_MoveDownRotatingBanner TO PUBLIC
GO


